package com.example.test;

import com.example.service.ExcelReader;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author wangwenlong@dhcc.com.cn
 * @description:
 * @date 2023/11/24 20:04
 */
@SpringBootTest
public class Demo1 {

    @Autowired
    private ExcelReader excelReader;

    /**
     * 变为大写
     */
    @Test
    public void testUptoCase(){
        String s = "processId";
        StringBuilder str = new StringBuilder();
        for (int i = 0; i < s.length(); i++) {
            if (s.charAt(i)>='a'&&s.charAt(i)<='z'){
                str.append(Character.toChars(s.charAt(i)-32));
            }
            if (s.charAt(i)>='A'&&s.charAt(i)<='Z'){
                str.append("_"+s.charAt(i));
            }
        }
        System.out.println(str.toString());
    }

    /**
     * 获取修改的表结构的语句
     * @throws IOException
     */
    @Test
    public void testChangeSql() throws IOException {
        //获取Excel读取的模版内容
        List<List<String>> lists = excelReader.readExcel("/Users/wang/Documents/workspace/家族项目/doc/excel/测试.xlsx");

        //传入修改的表名
        String tableName = "KYC_INV_PRD_INF";

        //存放所有的字段名
        List<String> fieldList = new ArrayList<>();
        //存放所有的备注
        List<String> remarkList = new ArrayList<>();
        //存放所有的类型
        List<String> typeList = new ArrayList<>();
        for (List<String> list : lists) {
            fieldList.add(list.get(0));
            String temp = "";
            if ("C".equalsIgnoreCase(String.valueOf(list.get(1).charAt(0)))){
                temp = list.get(1).substring(1);
                typeList.add("VARCHAR2("+temp+")");
            }else if ("D".equalsIgnoreCase(String.valueOf(list.get(1).charAt(0)))){
                typeList.add("NUMBER("+temp+")");
            }
            remarkList.add(list.get(2));
        }
        //获取变为大写后的字段名
        List<String> filedNameList = getFiledNameList(fieldList);
        String filedString = "";
        for (int i = 0; i < filedNameList.size(); i++) {
            filedString += filedNameList.get(i) + " "+ typeList.get(i) + ",\n";
            System.out.println("COMMENT ON COLUMN "+tableName+"."+filedNameList.get(i)+" is "+"'"+remarkList.get(i)+"';");
        }

        filedString = filedString.substring(0, filedString.length() - 1);

        String sql = "ALTER TABLE " + tableName + " ADD ("+filedString + ");";

        System.out.println(sql);
    }

    /**
     * 转为大写
     * @throws IOException
     */
    @Test
    public void testChange() throws IOException {
        //获取Excel读取的模版内容
        List<List<String>> lists = excelReader.readExcel("/Users/wang/Documents/workspace/家族项目/doc/excel/北京银行原型对照.xlsx");
        //存放所有的字段名
        List<String> fieldList = new ArrayList<>();
        for (List<String> list : lists) {
            fieldList.add(list.get(1));
        }
        List<String> filedNameList = getFiledNameList(fieldList);
        filedNameList.forEach(System.out::println);
    }

    /**
     * 将驼峰命名转为表字段的名称，带有_
     * @param fieldList 传入驼峰的名称
     * @return 返回表名List
     */
    private static List<String> getFiledNameList(List<String> fieldList) {
        List<String> nameList = new ArrayList<>();

        for (String s : fieldList) {
            StringBuilder str = new StringBuilder();
            for (int i = 0; i < s.length(); i++) {
                if (s.charAt(i)>='a'&&s.charAt(i)<='z'){
                    str.append(Character.toChars(s.charAt(i)-32));
                }else if (s.charAt(i)>='A'&&s.charAt(i)<='Z'&& i!=0){
                    str.append("_"+s.charAt(i));
                }else {
                    str.append(s.charAt(i));
                }
            }
            nameList.add(str.toString());
        }
        return nameList;
    }


    @Test
    public void testChangeUpdateSql() throws IOException {
        //获取Excel读取的模版内容
        List<List<String>> lists = excelReader.readExcel("/Users/wang/Documents/workspace/家族项目/doc/excel/北京银行原型对照.xlsx");


        //存放所有的字段名
        List<String> fieldList = new ArrayList<>();
        //存放所有的备注
        List<String> remarkList = new ArrayList<>();
        //存放所有的类型
        List<String> typeList = new ArrayList<>();
        for (List<String> list : lists) {
            fieldList.add(list.get(1));
            remarkList.add(list.get(5));
        }
        //获取变为大写后的字段名
        List<String> filedNameList = getFiledNameList(fieldList);
        String filedString = "";
        for (int i = 0; i < fieldList.size(); i++) {
            if (fieldList.get(i)!=null&&remarkList.get(i)!=null){
                System.out.println(fieldList.get(i)+"=#"+remarkList.get(i)+"#,");
            }
        }

        filedString = filedString.substring(0, filedString.length() - 1);

    }

    @Test
    public void test1(){
//        String s = "WTRXX\u001F2023110900000228\u001F委托人名称\u001F1\u001F156\u001F\u001F1\u001F\u001F\u001F0\u001F330800199301010001\u001F20230101\u001F23240101\u001F19930101\u001F1\u001F\u001F\u001F\u001FA\u001F11\u001F健康\u001F13111111111\u001F111@qq.com\u001F140000\u001F140100\u001F140101\u001F具体地址信息\u001F111111\u001F\u001F上海银行\u001F1111111111\u001F\u001F\u001F1\u001F1\u001F\u001F\u001F配偶名称\u001F156\u001F\u001F1\u001F1\u001F0\u001F330800199301010003\u001F20230101\u001F20240101\u001F20210101\u001FA\u001F11\u001F1\u001F紧急联系人名称\u001F13111111111\u001F公司名称\u001F10\u001F11111\u001F1\u001FtrustTermType\u001F10\u001F\u001F\u001F1\u001F20230101\u001F1\u001F140000\u001F140100\u001F140101\u001F地址信息\u001FA\u001F公司名称\u001F12\u001F\u001F\u001FInvStageType\u001FY";
//        String s = "WTRXX\u001F2023110900000228\u001F委托人名称\u001F1\u001F156\u001F\u001F1\u001F\u001F\u001F0\u001F330800199301010001\u001F20230101\u001F23240101\u001F19930101\u001F1\u001F\u001F\u001F\u001FA\u001F11\u001F健康\u001F13111111111\u001F111@qq.com\u001F140000\u001F140100\u001F140101\u001F具体地址信息\u001F111111\u001F\u001F上海银行\u001F1111111111\u001F\u001F\u001F1\u001F1\u001F\u001F\u001F配偶名称\u001F156\u001F\u001F1\u001F1\u001F0\u001F330800199301010003\u001F20230101\u001F20240101\u001F20210101\u001FA\u001F11\u001F1\u001F紧急联系人名称\u001F13111111111\u001F公司名称\u001F10\u001F11111\u001F1\u001FtrustTermType\u001F10\u001F\u001F\u001F1\u001F20230101\u001F1\u001F140000\u001F140100\u001F140101\u001F地址信息\u001FA\u001F公司名称\u001F12\u001F\u001F\u001FInvStageType\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\n";
//        String s = "SYRXX\u001F2023110900000228\u001F2023111300000684\u001F02\u001F1\u001FrelationOth\u001F受益人名称\u001F1\u001F1\u001F156\u001F\u001F1\u001F0\u001F330800199301010001\u001F20230101\u001F20240101\u001F20230101\u001F1\u001F1\u001F\u001F\u001F11\u001F13111111111\u001F111@qq.com\u001F140000\u001F140100\u001F140101\u001F具体地址信息\u001F0.4400\u001F\u001F\u001F\u001F开户名称\u001F银行名称\u001F1212331\u001F1\u001F1\u001F1\u001F联系人名称\u001F13111111111\u001F公司名称\u001F14\u001F140000\u001F140100\u001F\u001F\u001F1\u001F\u001F\n";
//        String s = "FPFAXX\u001F2023110900000228\u001F\u001F2023111300000684\u001F1\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F\u001F100.00\u001F\u001F\u001F\u001F\u001F\n";
        String seprator = "\u001F";
        String[] split = seprator.split(seprator,-1);
        System.out.println(split.length);
    }
}
